﻿using System.Collections;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

using WHC.Security.Entity;
using WHC.Security.IDAL;
using Microsoft.Practices.EnterpriseLibrary.Data;
using WHC.Security.Common;
using WHC.OrderWater.Commons;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace WHC.Security.DALSQL
{
    public class User : BaseDAL<UserInfo>, IUser
    {
        #region 对象实例及构造函数

        public static User Instance
        {
            get
            {
                return new User();
            }
        }
        public User()
            : base("T_ACL_User", "ID")
        {
        }

        #endregion

        /// <summary>
        /// 将DataReader的属性值转化为实体类的属性值，返回实体类
        /// </summary>
        /// <param name="dr">有效的DataReader对象</param>
        /// <returns>实体类对象</returns>
        protected override UserInfo DataReaderToEntity(IDataReader dataReader)
        {
            UserInfo userInfo = new UserInfo();
            SmartDataReader reader = new SmartDataReader(dataReader);

            userInfo.ID = reader.GetInt32("ID");
            userInfo.PID = reader.GetInt32("PID");
            userInfo.Name = reader.GetString("Name");
            userInfo.Password = reader.GetString("Password");
            userInfo.FullName = reader.GetString("FullName");
            userInfo.IsExpire = reader.GetBoolean("IsExpire");
            userInfo.Title = reader.GetString("Title");
            userInfo.IdentityCard = reader.GetString("IdentityCard");
            userInfo.MobilePhone = reader.GetString("MobilePhone");
            userInfo.OfficePhone = reader.GetString("OfficePhone");
            userInfo.HomePhone = reader.GetString("HomePhone");
            userInfo.Email = reader.GetString("Email");
            userInfo.Address = reader.GetString("Address");
            userInfo.CustomField = reader.GetString("CustomField");

            return userInfo;
        }

        /// <summary>
        /// 将实体对象的属性值转化为Hashtable对应的键值
        /// </summary>
        /// <param name="obj">有效的实体对象</param>
        /// <returns>包含键值映射的Hashtable</returns>
        protected override Hashtable GetHashByEntity(UserInfo obj)
        {
            UserInfo info = obj as UserInfo;
            Hashtable hash = new Hashtable();

            hash.Add("PID", info.PID);
            hash.Add("Name", info.Name);
            hash.Add("Password", info.Password);
            hash.Add("FullName", info.FullName);
            hash.Add("IsExpire", info.IsExpire);
            hash.Add("Title", info.Title);
            hash.Add("IdentityCard", info.IdentityCard);
            hash.Add("MobilePhone", info.MobilePhone);
            hash.Add("OfficePhone", info.OfficePhone);
            hash.Add("HomePhone", info.HomePhone);
            hash.Add("Email", info.Email);
            hash.Add("Address", info.Address);
            hash.Add("CustomField", info.CustomField);

            return hash;
        }

		public new bool  DeleteByKey(string key)
		{
               SqlDatabase db = new SqlDatabase(connectionString); 
            DbCommand command = null;

            DbTransaction transaction = null;
			try
			{
                UserInfo info = this.FindByID(key);
                if (info != null)
                {
                    using (DbConnection connection = db.CreateConnection())
                    {
                        connection.Open();
                        transaction = connection.BeginTransaction();

                        string commandText = string.Format("UPDATE [T_ACL_User] SET PID={0} Where PID={1}", info.PID, key);
                        command = db.GetSqlStringCommand(commandText);
                        db.ExecuteNonQuery(command, transaction);

                        commandText = string.Format("Delete From  [T_ACL_User] Where ID ={0} ", key);
                        command = db.GetSqlStringCommand(commandText);
                        db.ExecuteNonQuery(command, transaction);

                        transaction.Commit();
                    }
                }
			}
			catch
			{
                if (transaction != null)
                {
                    transaction.Rollback();
                    throw;
                }
			}
			return true;
		}

		private List<SimpleUserInfo> FillSimpleUsers(string sql)
		{
               SqlDatabase db = new SqlDatabase(connectionString); 
            DbCommand command = db.GetSqlStringCommand(sql);

            List<SimpleUserInfo> list = new List<SimpleUserInfo>();
			using (IDataReader reader = db.ExecuteReader(command))
			{
				while (reader.Read())
				{
					SimpleUserInfo info = new SimpleUserInfo();
					info.ID = (int) reader["ID"];
					info.Name = (string) reader["Name"];
					info.Password = (string) reader["Password"];
					info.FullName = (string) reader["FullName"];
					list.Add(info);
				}
				reader.Close();
			}
			return list;
		}

        private List<UserInfo> FillUsers(string sql)
		{
            List<UserInfo> list = new List<UserInfo>();

               SqlDatabase db = new SqlDatabase(connectionString); 
            DbCommand command = db.GetSqlStringCommand(sql);
            using (IDataReader dataReader = db.ExecuteReader(command))
			{
				while (dataReader.Read())
				{
					list.Add(this.DataReaderToEntity(dataReader));
				}
				dataReader.Close();
			}
			return list;
		}

		public UserInfo GetParent(int parentID)
		{
			return base.FindSingle("ID=" + parentID);
		}

        public List<SimpleUserInfo> GetSimpleUsers()
		{
			string sql = "Select ID,Name,Password,FullName From " + base.TableName;
			return this.FillSimpleUsers(sql);
		}

        public List<SimpleUserInfo> GetSimpleUsers(string userIDs)
		{
			string sql = "Select ID,Name,Password,FullName From " + base.TableName + " Where ID In (" + userIDs + ")";
			return this.FillSimpleUsers(sql);
		}

        public List<SimpleUserInfo> GetSimpleUsersByOU(int ouID)
		{
            string sql = "Select ID,Name,Password,FullName From T_ACL_OU_User Inner Join [T_ACL_User] ON [T_ACL_User].ID=User_ID Where OU_ID = " + ouID;
			return this.FillSimpleUsers(sql);
		}

        public List<SimpleUserInfo> GetSimpleUsersByRole(int roleID)
		{
            string sql = "Select ID,Name,Password,FullName From [T_ACL_User] INNER JOIN T_ACL_User_Role ON [T_ACL_User].ID=User_ID Where Role_ID = " + roleID;
			return this.FillSimpleUsers(sql);
		}

        public List<UserInfo> GetUsersByOU(int ouID)
		{
            string sql = "SELECT * FROM [T_ACL_User] INNER JOIN T_ACL_OU_User On [T_ACL_User].ID=T_ACL_OU_User.User_ID WHERE OU_ID = " + ouID;
			return this.FillUsers(sql);
		}

        public List<UserInfo> GetUsersByRole(int roleID)
		{
            string sql = "SELECT * FROM [T_ACL_User] INNER JOIN T_ACL_User_Role On [T_ACL_User].ID=T_ACL_User_Role.User_ID WHERE Role_ID = " + roleID;
			return this.FillUsers(sql);
		}
	}
}